const express = require('express');
const router = express.Router();
const db = require('../config/db');
const bcrypt = require('bcryptjs');
const schedule = require('node-schedule');
const jwt = require('jsonwebtoken');
const { generateToken } = require('../utils/verifyToken'); //token验证
const { getLocalTime, getRandom4DigitNumber } = require('../utils/utils');


// 每月1号凌晨1点执行任务
schedule.scheduleJob('0 1 1 * *', async () => {
  // schedule.scheduleJob('*/1 * * * *', async () => { 
  // 将所有记录的 contnumber 字段更新为 1
  const [result] = await db.query(
    'UPDATE count SET contnumber = 1'
  );

  console.log(`成功更新了 ${result.affectedRows} 条记录`);
  // const currentDate = new Date();
  // const dbName = getDatabaseNameByDate(currentDate); //根据当前时间生成数据库名称  当月最新名称 db_2025_03
  // const previousDbName = getPreviousDatabaseName(currentDate); // 获取上一个月的数据库名称 db_2025_02
  // await createDatabaseAndTable(dbName);  // 创建新数据库和表结构
  // // 同步上一个月的 IpAddressTable 和 temfilednamemap 装置表的 数据到新表
  // await syncIpAddressTableData(dbName, previousDbName);
});


// 注册接口
router.post('/register', async (req, res) => {
  try {
    const { username, password, email } = req.body;

    // 检查用户是否已存在
    const [existing] = await db.query('SELECT * FROM users WHERE username = ?', [username]);
    if (existing.length > 0) {
      return res.status(400).json({ message: '用户名已存在' });
    }

    // 密码加密
    const hashedPassword = await bcrypt.hash(password, 10);

    // 插入新用户
    await db.query(
      'INSERT INTO users (username, password, email) VALUES (?, ?, ?)',
      [username, hashedPassword, email]
    );

    res.status(201).json({ message: '注册成功' });
  } catch (error) {
    res.status(500).json({ message: '注册失败', error });
  }
});

// 登录接口
router.post('/login', async (req, res) => {
  const { username, password } = req.body;
  console.log(req.body)
  // 查找用户
  const [users] = await db.query('SELECT * FROM users WHERE username = ?', [username]);
  const user = users[0];
  if (users.length === 0 || password !== user.password) {
    return res.send({
      code: 500,
      msg: "用户名或密码错误"
    });
  }
  // 检查环境变量是否存在
  if (!process.env.JWT_SECRET) {
    throw new Error('JWT_SECRET is not defined');
  }
  const token = generateToken({ password: user.password, username: user.username });
  res.send({
    code: 200,
    data: {
      access_token: token
    },
    msg: "成功"
  });
});

function formatAmount(input) {
  // 2. 转换为数字（处理字符串输入）
  const num = Number(input);

  // 3. 格式化为 2 位小数
  return num.toFixed(2);
}
// 添加
router.post('/add', async (req, res) => {
  try {
    // username: 合同编号 gender：承包商 kaihuStut:开户行 email ：银行账号 hetongjine： 合同金额  fukuanzonge：付款总额
    console.log("🚀 ~ 添加 申请单:", req.body)
    const { username, gender, kaihuname, backname, hetongjine, fukuanzonge } = req.body;
    const timestamp = getLocalTime()
    if (isNaN(hetongjine) || isNaN(fukuanzonge)) {
      return res.send({
        code: 500,
        msg: "合同金额或付款金额输入的不是有效数字"
      });
    }
    // 插入新数据
    await db.query(
      'INSERT INTO lista (username, gender,kaihuname,backname,hetongjine,fukuanzonge,createTime) VALUES (?,?,?,?,?,?,?)',
      [username, gender, kaihuname, backname, formatAmount(hetongjine), formatAmount(fukuanzonge), timestamp]
    );

    return res.send({
      code: 200,
      msg: "成功"
    });

  } catch (error) {
    console.log(error);
    res.status(500).json({ message: '获取列表失败', error });
  }
});

async function getUniqueRandom4DigitNumber(db, maxAttempts = 100) {
  let attempts = 0;

  while (attempts < maxAttempts) {
    const genderValue = getRandom4DigitNumber();

    // const genderValue = 5352;
    const [rows] = await db.query('SELECT * FROM changbaoshang WHERE genderValue = ?', [genderValue]);

    if (rows.length === 0) {
      return genderValue;
    }
    attempts++;
  }

  throw new Error(`无法在${maxAttempts}次尝试内找到唯一的4位随机数`);
}

// 添加 承包商
router.post('/addchenbao', async (req, res) => {
  try {
    const { genderLabel, kaihuname, backname } = req.body;
    console.log("====添加 承包商===>", req.body)

    // 检查舱室是否在数据库中存在
    const [existing] = await db.query('SELECT * FROM changbaoshang WHERE genderLabel = ?', [genderLabel]);
    if (existing.length > 0) {
      return res.send({
        code: 500,
        msg: "承包商已存在，不可重复添加"
      });
    }
    const weiRound = await getUniqueRandom4DigitNumber(db)

    const timestamp = getLocalTime()
    // 插入新数据
    await db.query(
      'INSERT INTO changbaoshang (genderLabel, genderValue,kaihuname, backname,createTime) VALUES (?,?,?,?,?)',
      [genderLabel, weiRound, kaihuname, backname, timestamp]
    );

    return res.send({
      code: 200,
      msg: "成功"
    });

  } catch (error) {
    console.log(error);
    res.status(500).json({ message: '获取列表失败', error });
  }
});

// 修改
router.post('/edit', async (req, res) => {
  const { id, username, gender, kaihuname, backname, hetongjine, fukuanzonge } = req.body;
  console.log("🚀 ~ 修改:", req.body)

  // 检查必填字段
  if (!id || !gender || !kaihuname || !backname || !hetongjine || !fukuanzonge) {
    return res.status(400).json({ code: 400, msg: '缺少必填字段' });
  }
  const timestampup = getLocalTime()
  if (isNaN(hetongjine) || isNaN(fukuanzonge)) {
    return res.send({
      code: 500,
      msg: "合同金额或付款金额输入的不是有效数字"
    });
  }
  try {
    // 定义 SQL 更新语句
    let sql = `UPDATE lista SET username = ?,gender = ?, kaihuname = ?,  backname = ?,  hetongjine = ?,  fukuanzonge = ? , updataTime = ? WHERE id = ?`;
    // 执行 SQL 更新
    const [result] = await db.query(sql, [username, gender, kaihuname, backname, formatAmount(hetongjine), formatAmount(fukuanzonge), timestampup, id]);
    // 检查是否更新成功
    if (result.affectedRows === 0) {
      return res.status(404).json({ code: 404, msg: '未找到对应记录' });
    }
    // 返回成功响应
    res.status(200).json({ code: 200, msg: '更新成功' });
  } catch (error) {
    console.error('更新失败:', error);
    res.status(500).json({ code: 500, msg: '更新失败', error: error.message });
  }
});

// 修改承包商
router.post('/editchenbao', async (req, res) => {
  const { id, genderLabel, genderValue, kaihuname, backname } = req.body;
  console.log("🚀 ~ 修改承包商:", req.body)
  // 检查必填字段
  if (!id || !genderLabel || !genderValue || !kaihuname || !backname) {
    return res.send({
      code: 500,
      msg: "缺少必填字段"
    });
  }
  const timestampup = getLocalTime()
  try {
    // 定义 SQL 更新语句
    let sql = `UPDATE changbaoshang SET genderLabel = ?,kaihuname = ?,backname = ?, updataTime = ? WHERE id = ?`;
    // 执行 SQL 更新
    const [result] = await db.query(sql, [genderLabel, kaihuname, backname, timestampup, id]);
    // 检查是否更新成功
    if (result.affectedRows === 0) {
      return res.status(404).json({ code: 404, msg: '未找到对应记录' });
    }
    // 返回成功响应
    res.status(200).json({ code: 200, msg: '更新成功' });
  } catch (error) {
    console.error('更新失败:', error);
    res.status(500).json({ code: 500, msg: '更新失败', error: error.message });
  }
});



// 删除接口 没问题
router.post('/delete', async (req, res) => {
  try {
    const { id } = req.body
    console.log("🚀 ~ 删除 :", id)
    const placeholders = id.map(() => '?').join(','); // 生成占位符，例如 "?, ?"
    const sql = `delete from lista where id in (${placeholders})`
    await db.query(sql, id);
    return res.send({
      code: 200,
      msg: "成功"
    });

  } catch (error) {
    res.status(500).json({ message: '获取列表失败', error });
  }
})
// 删除承包商接口 没问题
router.post('/deletechenbao', async (req, res) => {
  try {
    const { id } = req.body
    console.log("🚀 ~ 删除承包商:", id)
    const placeholders = id.map(() => '?').join(','); // 生成占位符，例如 "?, ?"
    const sql = `delete from changbaoshang where id in (${placeholders})`
    await db.query(sql, id);
    return res.send({
      code: 200,
      msg: "成功"
    });

  } catch (error) {
    res.status(500).json({ message: '获取列表失败', error });
  }
})



// 获取 变电柜列表  没问题
router.post('/list', async (req, res) => {

  // 获取前端的参数
  let { id, username, gender, kaihuStut, pageNum, pageSize } = req.body;
  pageNum = (pageNum - 1) * pageSize;
  let conditions = [];
  let values = [];
  if (username) {
    conditions.push(`username like '%${username}%'`);
    values.push(`%${username}%`);
  }
  if (gender) {
    conditions.push(`gender = '${gender}'`);
    values.push(gender);
  }
  if (kaihuStut) {
    conditions.push(`kaihuStut = '${kaihuStut}'`);
    values.push(kaihuStut);
  }
  let whereClause = "";
  if (conditions.length > 0) {
    whereClause = "where " + conditions.join(" and ");
  }

  // 查询列表sql
  const pageSql = `select * from lista ${whereClause} order by id limit ${pageNum},${pageSize}`;
  // 查询总数的sql
  const totalSql = `select count(*) as total from lista ${whereClause}`;

  try {
    const [rows] = await db.query(pageSql);
    const [total] = await db.query(totalSql);
    const totalRecords = total[0].total; // 数据库中的总记录数
    const pages = Math.ceil(totalRecords / pageSize); // 计算总页数
    res.json({
      code: 200,
      msg: "成功",
      data: {
        list: rows,
        pageNum: 1,
        pageSize: 10,
        total: total[0].total,
      }

    });
  } catch (error) {
    res.status(500).json({ message: '获取媒体列表失败', error });
  }
});

// 获取 承包商 字典
router.get('/gender', async (req, res) => {
  // 获取前端的参数
  let pageNum = 1
  let pageSize = 1000
  pageNum = (pageNum - 1) * pageSize;
  let conditions = [];
  let whereClause = "";
  if (conditions.length > 0) {
    whereClause = "where " + conditions.join(" and ");
  }

  // 查询列表sql
  const pageSql = `select * from changbaoshang ${whereClause} order by id limit ${pageNum},${pageSize}`;
  // 查询总数的sql
  const totalSql = `select count(*) as total from changbaoshang ${whereClause}`;

  try {
    const [rows] = await db.query(pageSql);
    const [total] = await db.query(totalSql);
    const totalRecords = total[0].total; // 数据库中的总记录数
    const pages = Math.ceil(totalRecords / pageSize); // 计算总页数
    res.json({
      code: 200,
      msg: "成功",
      data: rows

    });
  } catch (error) {
    res.status(500).json({ message: '获取媒体列表失败', error });
  }
});

// 获取 承包商
router.post('/listchenbao', async (req, res) => {
  // 获取前端的参数
  let { genderLabel, pageNum, pageSize } = req.body;
  pageNum = (pageNum - 1) * pageSize;
  let conditions = [];
  let values = [];
  if (genderLabel) {
    conditions.push(`genderLabel like '%${genderLabel}%'`);
    values.push(`%${genderLabel}%`);
  }
  let whereClause = "";
  if (conditions.length > 0) {
    whereClause = "where " + conditions.join(" and ");
  }

  // 查询列表sql
  const pageSql = `select * from changbaoshang ${whereClause} order by id limit ${pageNum},${pageSize}`;
  // 查询总数的sql
  const totalSql = `select count(*) as total from changbaoshang ${whereClause}`;

  try {
    const [rows] = await db.query(pageSql);
    const [total] = await db.query(totalSql);
    const totalRecords = total[0].total; // 数据库中的总记录数
    const pages = Math.ceil(totalRecords / pageSize); // 计算总页数
    res.json({
      code: 200,
      msg: "成功",
      data: {
        list: rows,
        pageNum: 1,
        pageSize: 10,
        total: total[0].total,
      }

    });
  } catch (error) {
    console.log("🚀 ~ 获取承包商列表失败:", error)
    res.status(500).json({ message: '获取媒体列表失败', error });
  }
});





// 重置密码接口
router.post('/reset-password', async (req, res) => {
  try {
    const { username, email, newPassword } = req.body;

    // 验证用户是否存在
    const [users] = await db.query('SELECT * FROM users WHERE username = ? AND email = ?', [username, email]);
    if (users.length === 0) {
      return res.status(400).json({ message: '用户名或邮箱不正确' });
    }

    const hashedPassword = await bcrypt.hash(newPassword, 10);

    // 更新用户密码
    await db.query('UPDATE users SET password = ? WHERE username = ?', [hashedPassword, username]);

    res.json({ message: '密码重置成功' });
  } catch (error) {
    res.status(500).json({ message: '密码重置失败', error });
  }
});





// 开户行模块

async function getUniqueRandom4DigitNumber2(db, maxAttempts = 100) {
  let attempts = 0;

  while (attempts < maxAttempts) {
    const genderValue = getRandom4DigitNumber();

    // const genderValue = 5352;
    const [rows] = await db.query('SELECT * FROM kaihuhang WHERE genderValue = ?', [genderValue]);

    if (rows.length === 0) {
      return genderValue;
    }
    attempts++;
  }

  throw new Error(`无法在${maxAttempts}次尝试内找到唯一的4位随机数`);
}

// 添加 开户行
router.post('/addkaihu', async (req, res) => {
  try {
    const { genderLabel } = req.body;
    const [existing] = await db.query('SELECT * FROM kaihuhang WHERE genderLabel = ?', [genderLabel]);
    if (existing.length > 0) {
      return res.send({
        code: 500,
        msg: "开户行已存在，不可重复添加"
      });
    }
    const weiRound = await getUniqueRandom4DigitNumber2(db)

    const timestamp = getLocalTime()
    // 插入新数据
    await db.query(
      'INSERT INTO kaihuhang (genderLabel, genderValue,createTime) VALUES (?,?,?)',
      [genderLabel, weiRound, timestamp]
    );

    return res.send({
      code: 200,
      msg: "成功"
    });

  } catch (error) {
    console.log(error);
    res.status(500).json({ message: '获取列表失败', error });
  }
});





// 删除承包商接口 没问题
router.post('/deletekaihu', async (req, res) => {
  try {
    const { id } = req.body
    const placeholders = id.map(() => '?').join(','); // 生成占位符，例如 "?, ?"
    const sql = `delete from kaihuhang where id in (${placeholders})`
    await db.query(sql, id);
    return res.send({
      code: 200,
      msg: "成功"
    });

  } catch (error) {
    res.status(500).json({ message: '获取列表失败', error });
  }
})

// 修改承包商
router.post('/editkaihu', async (req, res) => {
  const { id, genderLabel, genderValue } = req.body;
  // 检查必填字段
  if (!id || !genderLabel || !genderValue) {
    return res.send({
      code: 500,
      msg: "缺少必填字段"
    });
  }
  const timestampup = getLocalTime()
  try {
    // 定义 SQL 更新语句
    let sql = `UPDATE kaihuhang SET genderLabel = ?, updataTime = ? WHERE id = ?`;
    // 执行 SQL 更新
    const [result] = await db.query(sql, [genderLabel, timestampup, id]);
    // 检查是否更新成功
    if (result.affectedRows === 0) {
      return res.status(404).json({ code: 404, msg: '未找到对应记录' });
    }
    // 返回成功响应
    res.status(200).json({ code: 200, msg: '更新成功' });
  } catch (error) {
    console.error('更新失败:', error);
    res.status(500).json({ code: 500, msg: '更新失败', error: error.message });
  }
});

// 获取 开户行 字典
router.get('/kaihuType', async (req, res) => {
  // 获取前端的参数
  let pageNum = 1
  let pageSize = 1000
  pageNum = (pageNum - 1) * pageSize;
  let conditions = [];
  let whereClause = "";
  if (conditions.length > 0) {
    whereClause = "where " + conditions.join(" and ");
  }

  // 查询列表sql
  const pageSql = `select * from kaihuhang ${whereClause} order by id limit ${pageNum},${pageSize}`;
  // 查询总数的sql
  const totalSql = `select count(*) as total from kaihuhang ${whereClause}`;

  try {
    const [rows] = await db.query(pageSql);
    const [total] = await db.query(totalSql);
    const totalRecords = total[0].total; // 数据库中的总记录数
    const pages = Math.ceil(totalRecords / pageSize); // 计算总页数
    res.json({
      code: 200,
      msg: "成功",
      data: rows

    });
  } catch (error) {
    res.status(500).json({ message: '获取媒体列表失败', error });
  }
});


// 获取 开户行
router.post('/listkaihu', async (req, res) => {
  // 获取前端的参数
  let { genderLabel, pageNum, pageSize } = req.body;
  pageNum = (pageNum - 1) * pageSize;
  let conditions = [];
  let values = [];
  if (genderLabel) {
    conditions.push(`genderLabel like '%${genderLabel}%'`);
    values.push(`%${genderLabel}%`);
  }
  let whereClause = "";
  if (conditions.length > 0) {
    whereClause = "where " + conditions.join(" and ");
  }

  // 查询列表sql
  const pageSql = `select * from kaihuhang ${whereClause} order by id limit ${pageNum},${pageSize}`;
  // 查询总数的sql
  const totalSql = `select count(*) as total from changbaoshang ${whereClause}`;

  try {
    const [rows] = await db.query(pageSql);
    const [total] = await db.query(totalSql);
    const totalRecords = total[0].total; // 数据库中的总记录数
    const pages = Math.ceil(totalRecords / pageSize); // 计算总页数
    res.json({
      code: 200,
      msg: "成功",
      data: {
        list: rows,
        pageNum: 1,
        pageSize: 10,
        total: total[0].total,
      }

    });
  } catch (error) {
    res.status(500).json({ message: '获取媒体列表失败', error });
  }
});

//
router.post('/addNumber', async (req, res) => {
  const { type } = req.body; // 接收前端传递的 type 参数

  if (!type) {
    return res.status(400).json({
      success: false,
      message: '缺少 type 参数'
    });
  }

  try {
    // 1. 查询当前记录
    const [rows] = await db.query(
      'SELECT * FROM count WHERE type = ? FOR UPDATE',
      [type]
    );

    if (rows.length === 0) {
      // 如果没有记录，则创建新记录，初始值为 "001"
      const [result] = await db.query(
        'INSERT INTO count (contnumber, type) VALUES (?, ?)',
        ['001', type]
      );


      return res.json({
        success: true,
        message: '计数器已创建',
        contnumber: '001',
        id: result.insertId
      });
    }

    // 2. 获取当前 contnumber 并递增
    const currentRecord = rows[0];
    let currentNumber = parseInt(currentRecord.contnumber);
    currentNumber++;

    // 格式化为三位数，前面补零
    const newContNumber = currentNumber.toString().padStart(3, '0');

    // 3. 更新数据库
    await db.query(
      'UPDATE count SET contnumber = ? WHERE id = ?',
      [newContNumber, currentRecord.id]
    );


    res.json({
      success: true,
      message: '计数器已递增',
      contnumber: newContNumber,
      id: currentRecord.id
    });

  } catch (error) {

    console.error('计数器递增失败:', error);
    res.status(500).json({
      success: false,
      message: '计数器递增失败',
      error: error.message
    });
  } finally {
  }
});

router.post('/getconlist', async (req, res) => {
  const { type } = req.body; // 接收前端传递的 type 参数

  if (!type) {
    return res.status(400).json({
      success: false,
      message: '缺少 type 参数'
    });
  }

  try {
    // 1. 查询当前记录
    const [rows] = await db.query(
      'SELECT * FROM count WHERE type = ? FOR UPDATE',
      [type]
    );

    // 转换 contnumber 为三位字符串
    const formattedData = rows.map(item => ({
      ...item,
      contnumber: item.contnumber.toString().padStart(3, '0')
    }));


    res.json({
      success: true,
      message: '计数器已递增',
      data: formattedData
    });

  } catch (error) {

    console.error('计数器递增失败:', error);
    res.status(500).json({
      success: false,
      message: '计数器递增失败',
      error: error.message
    });
  } finally {
  }
})



// 导出数据模版下载
router.post('/export', async (req, res) => {
  // 获取前端的参数
  let { genderLabel, pageNum, pageSize } = req.body;
  try {
    res.json({
      code: 200,
      msg: "成功",
      data: [
        {
          "合同编号": "ME-PO-202506-0604AB",
          "承包商": "男性", // 将gender 7213转换为可读性更好的文本
          "开户行": "已开户", // 将kaihuStut 8300转换为可读性更好的文本
          "银行账号": "033 225 000 400 10789",
          "合同金额": "5648.40",
          "付款总额": "9648.40",
          "创建时间": "1983-05-23 16:35:39"
        },
        {
          "合同编号": "ME-PO-202506-0602AB",
          "承包商": "女性", // 将gender 5980转换为可读性更好的文本
          "开户行": "未开户", // 将kaihuStut 6652转换为可读性更好的文本
          "银行账号": "2205 0161 6638 0000 0511",
          "合同金额": "9480.00",
          "付款总额": "72480.00",
          "创建时间": "2025-05-23 16:35:39"
        }
      ]
    });
  } catch (error) {
    res.status(500).json({ message: '下载模版失败', error });
  }
});





module.exports = router;
